package com.news.db;

import java.sql.*;

/**
 * 连接数据库，crud方法（持久层）
 */
public class DB {
    //数据库连接要素
    private static final String DRIVER_CLASS = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String DRIVER_URL = "jdbc:sqlserver://localhost:1433;DatabaseName=db_news";
    private static final String USERNAME = "sa";
    private static final String PASSWORD = "123456";

    //数据库使用到的类
    private Connection conn = null;
    private PreparedStatement pStm = null;
    private ResultSet res = null;

    /**
     * 注册数据库驱动（构造方法）
     */
    public DB() {
        try {
            Class.forName(DRIVER_CLASS);
            //System.out.println("数据库驱动注册成功");
        } catch (Exception e) {
            //e.printStackTrace();
            System.err.print("数据库驱动注册失败！");
        }
    }

    /**
     * 数据库连接
     */
    private Connection getConn() {
        try {
            conn = DriverManager.getConnection(DRIVER_URL, USERNAME, PASSWORD);
        } catch (Exception e) {
            //e.printStackTrace();
            conn = null;
        }
        return conn;
    }

    /**
     * 关闭数据库
     */
    public void closeDB() {
        try {
            if (res != null) {
                res.close();
            }
            if (pStm != null) {
                pStm.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            //e.printStackTrace();
            res = null;
            pStm = null;
            conn = null;
        }
    }

    /**
     * 数据库crud
     *
     * @param sql    sql语句
     * @param params sql参数，为数据库实体类
     */
    public void doPstm(String sql, Object[] params) {
        if (sql != null) {
            if (params == null) {
                params = new Object[0];
            }
            //获取数据库连接对象
            conn = getConn();
            if (conn != null) {
                try {
                    //获取sql执行对象（预编译sql）
                    pStm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                    //遍历参数，并给sql占位符赋值
                    for (int i = 0; i < params.length; i++) {
                        pStm.setObject(i + 1, params[i]);
                    }
                    //执行sql语句
                    pStm.execute();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 获取执行操作后影响的记录数
     */
    public int getCounts() {
        int count = -1;
        try {
            count = pStm.getUpdateCount();
        } catch (Exception e) {
            count = -1;
        }
        return count;
    }

    /**
     * 获取结果集
     */
    public ResultSet getRes() {
        try {
            res = pStm.getResultSet();
        } catch (Exception e) {
            res = null;
        }
        return res;
    }

}
